package com.ruoyi.yys.util.excel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.hutool.core.collection.IterUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.ruoyi.common.constant.ExcelStants;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.yys.common.enums.ErrorStatus;
import com.ruoyi.yys.domain.ImportExportTask;
import com.ruoyi.yys.domain.export.DictVo;
import com.ruoyi.yys.domain.export.MergeVo;
import com.ruoyi.yys.domain.export.RowHeighVo;
import com.ruoyi.yys.domain.export.StyleVo;
import com.ruoyi.yys.mapper.ImportExportTaskMapper;
import com.ruoyi.yys.util.OssUtils;
import com.ruoyi.yys.util.XyUtils;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.util.*;

@Component
@RequiredArgsConstructor
public class ExcelUtils {
    private final ImportExportTaskMapper taskMapper;
    /**
     * 导出excel
     *
     * @param datas    数据
     * @param filePath 文件路径
     */
    public void exportExcel(List<Entity> datas, String filePath) {
        exportExcel(datas, filePath, null, null);
    }

    /**
     * 导出excel
     *
     * @param datas    数据
     * @param filePath 文件路径
     */
    public void exportExcel(List<Entity> datas, String filePath, StyleVo styleData) {
        exportExcel(datas, filePath, null, styleData);
    }

    /**
     * 导出excel
     *
     * @param datas    数据
     * @param filePath 文件路径
     */
    public void exportExcel(String id, List<Entity> datas, String filePath, StyleVo styleData) {
        exportExcel(id, datas, filePath, null, styleData, null);
    }

    /**
     * 导出excel
     *
     * @param datas    数据
     * @param filePath 文件路径
     */
    public void exportExcel(String id, List<Entity> datas, String filePath, StyleVo styleData, TimeInterval timer) {
        try {
            exportExcel(id, datas, filePath, null, styleData, timer);
        } catch (ServiceException e) {
            throw new ServiceException(e.getCode(), "生成excel出错：" + e.getMessage());
        }
    }

    public void exportExcelThrow(String id, List<Entity> datas, String filePath, DictVo dictVo, StyleVo styleData, TimeInterval timer) {
        try {
            exportExcel(id, datas, filePath, dictVo, styleData, timer);
        } catch (ServiceException e) {
            throw new ServiceException(e.getCode(), "生成excel出错：" + e.getMessage());
        }
    }

    /**
     * 导出excel
     * 导出excel 标题转换
     *
     * @param datas     数据
     * @param filePath  文件路径
     * @param dictVo     字典
     * @param id        id
     * @param styleData 风格数据
     * @param timer     计时器
     */
    public void exportExcel(String id, List<Entity> datas, String filePath, DictVo dictVo, StyleVo styleData, TimeInterval timer) {
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter(filePath);
        //标题转换
        if(null != dictVo){
            Map<String, String> title = dictVo.getTitle();
            if (MapUtil.isNotEmpty(title)) {
                title.forEach((k, v) -> writer.addHeaderAlias(k, Convert.toStr(v)));
            }
        }

        String group = XyUtils.getId32();
        //一次性写出内容，强制输出标题
        try {
            write(dictVo, styleData,id, writer, datas, true, timer,group);
        }catch (Exception e){
            throw new ServiceException("生成excel出错：" + e.getMessage(),ErrorStatus.EXCEPTION.getCode());
        }finally {
            //删除保存在本地的文件
            FileUtil.del(ExcelStants.EXCEL_PATH + group);
        }

        //设置列宽
        setColumWidth(styleData, writer,datas.get(0).keySet().size());
        //设置行高 这样写，会出现错误：该行还未完全写入数据，导致row为null，设置行高失败
        //setRowHeight(datas.size(), styleData, writer);
        //设置某列自动宽度
        setAutoSizeColumn(styleData, writer);
        //设置合并
        setMerges(styleData, writer);
        //更新进度
        taskMapper.updateImportExportTask(new ImportExportTask().setId(id).setOpError("数据全部处理完成，正在写入到excel中").setExecuteTime(XyUtils.converTime(timer.intervalSecond())));
        //关闭writer，释放内存
        writer.close();
    }

    /**
     * 设置合并
     *
     * @param styleData 风格数据
     * @param writer    作家
     */
    public void setMerges(StyleVo styleData, ExcelWriter writer) {
        if(ObjectUtil.isEmpty(styleData)){
            return;
        }
        List<MergeVo> merges = styleData.getMerges();
        if (IterUtil.isNotEmpty(merges)) {
            for (MergeVo merge : merges) {
                Integer firstColumn = merge.getFirstColumn();
                Integer firstRow = merge.getFirstRow();
                Integer lastColumn = merge.getLastColumn();
                Integer lastRow = merge.getLastRow();
                Object content = merge.getContent();
                if (ObjectUtil.isAllNotEmpty(firstColumn, firstRow, lastColumn, lastRow, content)) {
                    writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
                }
            }
        }
    }

    /**
     * 设置某列自动宽度
     *
     * @param styleData 风格数据
     * @param writer    作家
     */
    public void setAutoSizeColumn(StyleVo styleData, ExcelWriter writer) {
        if(ObjectUtil.isEmpty(styleData)){
            return;
        }
        List<Integer> autoSizeColumn = styleData.getAutoSizeColumn();
        if (IterUtil.isNotEmpty(autoSizeColumn)) {
            for (Integer k : autoSizeColumn) {
                //如果第一个为-1，表示所有列为自动大小，不再设置后面的数据 不考虑合并单元格
                if (k == -1) {
                    writer.autoSizeColumnAll();
                    break;
                } else {
                    writer.autoSizeColumn(autoSizeColumn.get(k));
                }
            }
        }
    }

    /**
     * 设置列宽
     *
     * @param styleData 风格数据
     * @param writer    作家
     */
    public void setColumWidth(StyleVo styleData, ExcelWriter writer, int keySize) {
        if(ObjectUtil.isEmpty(styleData)){
            return;
        }
        Map<Integer, Integer> columnWidth = styleData.getColumnWidth();
        if(MapUtil.isEmpty(columnWidth)){
            return;
        }
        //具体列设置具体宽度，其余行高就是-1的高度
        Integer negativeOne = columnWidth.keySet().stream().filter(e -> e == -1).findFirst().get();
        //默认列宽
        int defaultColumnWidth = ExcelStants.DEFAULT_COLUMN_WIDTH;
        if(null != negativeOne){
            defaultColumnWidth = columnWidth.get(negativeOne);
        }
        Set<Integer> keyStyle = columnWidth.keySet();
        for (int i = 0; i < keySize; i++) {
            int columnWidthNow = defaultColumnWidth;
            if(keyStyle.contains(i)){
                columnWidthNow = columnWidth.get(i);
            }
            writer.setColumnWidth(i, columnWidthNow);
        }
    }

    /**
     * 设置行高
     *
     * @param size     数据大小
     * @param styleData 风格数据
     * @param writer    作家
     */
    public void setRowHeight(int size, StyleVo styleData, ExcelWriter writer) {
        if(ObjectUtil.isEmpty(styleData)){
            return;
        }
        Map<Integer, Integer> rowHeigh = styleData.getRowHeigh();
        if(MapUtil.isEmpty(rowHeigh)){
            return;
        }
        if (MapUtil.isNotEmpty(rowHeigh)) {
            //具体行设置具体高度，其余行高就是-1的高度
            Integer negativeOne = rowHeigh.keySet().stream().filter(e -> e == -1).findFirst().get();
            //默认行高
            int defaultRowHeight = ExcelStants.DEFAULT_ROW_HEIGHT;
            if(null != negativeOne){
                defaultRowHeight = rowHeigh.get(negativeOne);
            }
            Set<Integer> rowHeighKeys = rowHeigh.keySet();
            //size + 1 表示加上标题的一行
            for (int i = 0; i < size + 1; i++) {
                int rowHeight = defaultRowHeight;
                if(rowHeighKeys.contains(i)){
                    rowHeight = rowHeigh.get(i);
                }
                writer.setRowHeight(i,rowHeight);
            }
        }
    }


    /**
     * 导出excel 标题转换
     *
     * @param datas    数据
     * @param filePath 文件路径
     * @param dictVo    字典
     */
    public void exportExcel(List<Entity> datas, String filePath, DictVo dictVo, StyleVo styleData) {
        exportExcel(null, datas, filePath, dictVo, styleData, null);
    }

    /**
     * 写
     *
     * @param dictVo               字典
     * @param id               id
     * @param writer           作家
     * @param datas            数据
     * @param isWriteKeyAsHead 写关键负责人吗
     * @param timer            计时器
     */
    public void write(DictVo dictVo, StyleVo styleData, String id, ExcelWriter writer, List<Entity> datas, boolean isWriteKeyAsHead, TimeInterval timer,String group) {
        //总数据量
        int size = datas.size();
        //进度
        int rate = 0;
        //是否为第一行，第一行写入标题
        boolean isFirst = true;
        //解析图片列索引
        Map<String, Integer> imgsColumIndex = analysisImgs(dictVo, datas.get(0));
        //获取行高数据
        RowHeighVo rowHeight = getRowHeight(styleData);
        for (int i = 0; i < datas.size(); i++) {
            //是否超时
            if (timer.intervalMinute() >= ExcelStants.ACTIVE_TIMEOUT_STATUS) {
                throw new ServiceException(ErrorStatus.TIMEOUT.getInfo(), ErrorStatus.TIMEOUT.getCode());
            }
            //计算进度百分比
            String percentStr = NumberUtil.formatPercent(NumberUtil.div(i + 1, size), 2);
            Integer percent = Convert.toInt(percentStr);
            if (percent >= (rate + 10) || percent == 100) {
                rate = percent;
                taskMapper.updateProgress(id, percentStr, XyUtils.converTime(timer.intervalSecond()));
            }

            Entity entity = datas.get(i);
            //写图片
            if(MapUtil.isNotEmpty(imgsColumIndex)){
                for (String imgKey : imgsColumIndex.keySet()) {
                    //压缩图片
                    File picFile = OssUtils.getPicFile_q(group,entity.getStr(imgKey),ExcelStants.IMG_YS);
                    if(ObjectUtil.isNotEmpty(picFile)){
                        writer.writeImg(picFile, imgsColumIndex.get(imgKey), i + 1, imgsColumIndex.get(imgKey) + 1, i + 1 + 1);
                    }
                    //将该格设置为空
                    entity.set(imgKey,"");
                }
            }
            writer.writeRow(entity, isFirst && isWriteKeyAsHead);
            //设置行高
            setRowHight(writer, rowHeight.getDefaultRowHeight(), rowHeight.getRowHeighKeys(), rowHeight.getRowHeigh(), i);
            if (isFirst) {
                isFirst = false;
            }
        }
        //设置最后一行的行高，标题行多占了一行
        setRowHight(writer, rowHeight.getDefaultRowHeight(), rowHeight.getRowHeighKeys(), rowHeight.getRowHeigh(), size);
    }

    /**
     * 得到行高数据
     *
     * @param styleData 风格数据
     * @return {@link Dict}
     */
    public static RowHeighVo getRowHeight(StyleVo styleData){
        //默认行高
        int defaultRowHeight = ExcelStants.DEFAULT_ROW_HEIGHT;
        //行高keys
        Set<Integer> rowHeighKeys = null;
        //获取行高参数
        Map<Integer, Integer> rowHeigh = null;
        if(ObjectUtil.isNotEmpty(styleData)){
            rowHeigh = styleData.getRowHeigh();
            if(MapUtil.isNotEmpty(rowHeigh)){
                //如果参数有默认行高，替换默认行高
                Integer negativeOne = rowHeigh.keySet().stream().filter(e -> e == -1).findFirst().get();
                if(null != negativeOne){
                    defaultRowHeight = rowHeigh.get(negativeOne);
                }
                rowHeighKeys = rowHeigh.keySet();
            }
        }
        return new RowHeighVo(defaultRowHeight,rowHeighKeys,rowHeigh);
    }

    /**
     * 设置行高
     *
     * @param writer           作家
     * @param defaultRowHeight 默认行高
     * @param rowHeighKeys     嗨!行键
     * @param rowHeigh         行嗨!
     * @param i                我
     */
    public static void setRowHight(ExcelWriter writer, int defaultRowHeight, Set<Integer> rowHeighKeys, Map<Integer, Integer> rowHeigh, int i) {
        //设置行高
        int rowHeight = defaultRowHeight;
        if (IterUtil.isNotEmpty(rowHeighKeys)) {
            if (rowHeighKeys.contains(i)) {
                rowHeight = rowHeigh.get(i);
            }
        }
        writer.setRowHeight(i, rowHeight);
    }

    /**
     * 获取所有图片的列索引
     *
     * @param dictVo dict签证官
     * @param entity 实体
     */
    public static Map<String, Integer> analysisImgs(DictVo dictVo, Entity entity) {
        Map<String, Integer> m = new HashMap<>();
        //获取所有图片的列索引
        if(ObjectUtil.isEmpty(dictVo)){
            return m;
        }
        Map<String, Integer> img = dictVo.getImg();
        if(MapUtil.isEmpty(img)){
            return m;
        }
        Set<String> imgs = img.keySet();
        if(IterUtil.isEmpty(imgs)){
            return m;
        }
        //解析该图片的具体列
        for (String imgKey : imgs) {
            int keyColumIndex = 0;
            for (String key : entity.keySet()) {
                if(imgKey.equals(key)){
                    m.put(key,keyColumIndex);
                }
                keyColumIndex++;
            }
        }
        return m;
    }

    /**
     * excel 导出
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       excel文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (ObjectUtil.isEmpty(file)) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerfiy  是否检验excel内容
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (ObjectUtil.isEmpty(inputStream)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        params.setNeedVerify(needVerfiy);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }
}